// 导入 express模块
const express = require('express');
// 导入body-parser中间件
const bodyParser = require('body-parser');
// 导入mysql2,用于连接数据库
const mysql = require('mysql2');
//md5加密
const md5 = require("md5")
// 创建express应用实例
const app = express();
// 设置端口号为3000
const PORT = 3000;

async function queryData(sql) {
    // 返回Promise
    return new Promise((resolve, reject) => {
        db.query(sql, (err, results) => {
            if (err) reject(err);
            resolve(results);
        });
    });
}

// 数据库配置
const DB_HOST = 'rm-cn-20p37k4s00024nio.rwlb.cn-chengdu.rds.aliyuncs.com';
const DB_USER = 'taritari';
const DB_PASSWORD = 'Rzw5165611';
const DB_NAME = 'blog_study';

// 数据库连接类
class DBConnection {
    constructor() {
        // 创建数据库连接池
        this.pool = mysql.createPool({
            host: DB_HOST,
            user: DB_USER,
            password: DB_PASSWORD,
            database: DB_NAME
        });
    }
    query(sql, callback) {  //执行sql语句
        this.pool.query(sql, callback);
    }
}
// 实例化数据库连接类  
const db = new DBConnection();

// 中间件
// 解析 application/json 
app.use(bodyParser.json());
// 解析 application/x-www-form-urlencoded  
app.use(express.urlencoded({ extended: true }));
// 解析json数据
app.use(express.json());
// 解决跨域问题
app.use((req, res, next) => {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "Content-Type");
    next();
});
//数据返回配置
function handleError(res) {
    res.status(500).send({
        code: 500,
        msg: res
    });
}

function sendData(res, data) {
    res.send({
        code: 200,
        data: data
    });
}

/**
 * ————————————————————————————————————————————上半部分，不需要进行修改————————————————————————————————————————————
 * */
// 获取product数据库总条数
app.get('/getProductCount', (req, res) => {
    let id = req.query.id;
    let search = req.query.search;
    let sql = ``;
    if (id == 0) {
        sql = `SELECT COUNT(*) as total FROM product`;
    } else {
        sql = `SELECT COUNT(*) as total FROM product WHERE product.productSeries=(SELECT seriesProduct FROM series WHERE id=${id});`;
    }
    console.log(search);
    db.query(sql, (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send({
                code: 500,
                msg: res
            });
        } else {
            res.send({
                code: 200,
                total: result[0].total
            });
        }
    });
});
//通过id删除产品
app.get('/deleteProductById', (req, res) => {
    var id = req.query.id;
    const sql = `DELETE FROM product WHERE id = ${id}`;
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//通过id删除产品
app.get('/deleteSalesById', (req, res) => {
    var id = req.query.id;
    const sql = `DELETE FROM sales WHERE productId = ${id};`;
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//获取产品中心图片推荐数据
app.get('/getProductCenter', (req, res) => {
    var count = req.query.count;
    const sql = `SELECT productName,imgSrc,id FROM product limit ${count}, 10`;
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//获取热门产品前5
app.get('/getHotProduct', (req, res) => {
    const sql = `SELECT product.id,CONCAT(product.productName,"       ",product.introduction) as introduction FROM product JOIN sales ON product.id = sales.productId ORDER BY sales.salesVolume desc LIMIT 5 `;
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});

//通过id查询产品详细信息
app.get('/getProductById', (req, res) => {
    var id = req.query.id;
    const sql = `SELECT * FROM product WHERE id = ${id}`;
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//产品详情页面关联产品列表获取
app.get('/getRelevanceProduct', (req, res) => {
    var productSeries = req.query.productSeries;
    const sql = `SELECT id,productName,imgSrc FROM product WHERE productSeries = "${productSeries}" LIMIT 12; `;
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//获取产品系列列表
app.get('/getSeriesList', (req, res) => {
    const sql = `SELECT * FROM series; `;
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
// 产品中心页面产品分页列表
app.post('/getProductPageInfo', (req, res) => {
    let { currPage, series,search } = req.body;
    let pageSize = currPage * 8;
    let sql = ``;
    if (series == 0) {
        sql = `SELECT product.*,sales.salesVolume FROM product JOIN sales ON product.id = sales.productId WHERE 1=1 LIMIT ${pageSize},8;`;
    }else if(search!=null){
        sql = `SELECT product.*,sales.salesVolume FROM product JOIN sales ON product.id = sales.productId WHERE 1=1 AND productName LIKE "%${search}%" OR message like "%${search}%" OR introduction LIKE "%${search}%" LIMIT ${pageSize},4;`;
    } else {
        sql = `SELECT product.*,sales.salesVolume FROM product JOIN sales ON product.id = sales.productId WHERE 1=1 AND product.productSeries=(SELECT seriesProduct FROM series WHERE id=${series}) LIMIT ${pageSize},8;`;
    }
    console.log(sql);
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//通过id获取系列名(product.html)
app.get('/getSeriesNameById', (req, res) => {
    let id = req.query.id;
    let sql = `SELECT seriesProduct FROM series WHERE id=${id}`;
    console.log(sql);
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result); 
    });
});
// 通过产品id查询产品评论
app.post('/getMainCommentByProductId', (req, res) => {
    let { id } = req.body;
    sql = `SELECT comment.*,users.title FROM comment JOIN users ON comment.user=users.userName WHERE productId =  ${id};`;
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//登录
app.post('/logins', (req, res) => {
    let { userName, pwd } = req.body;
    //对密码进行md5加密
    pwd = md5(pwd);
    sql = `SELECT * FROM users WHERE userName = "${userName}" AND password = "${pwd}"`;
    console.log("当前运行的sql是" + sql);
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//添加
app.post('/addProduct', (req, res) => {
    let { productName, productSeries, introduction, message, imgSrc } = req.body;
    sql = `INSERT INTO product VALUES(NULL,"${productName}","${productSeries}","${introduction}","${message}",NOW(),"${imgSrc}")`;
    console.log("当前运行的sql是" + sql);
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//添加产品后添加销量默认为0
app.get('/addSales', (req, res) => {
    let productId = req.query.productId;
    sql = `INSERT INTO sales VALUES(NULL,${productId},0)`;
    console.log("当前运行的sql是" + sql);
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//添加主评论
app.post('/addCommentByProductId', (req, res) => {
    let { user, comments, productId } = req.body;
    sql = `INSERT INTO comment VALUES(NULL,${productId},"${user}",NOW(),NULL,"${comments}",NULL);`;
    console.log("当前运行的sql是" + sql);
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//添加子评论（回复评论）
app.post('/addChildComment', (req, res) => {
    //commentId父Id
    let { user, productId, commentId, child } = req.body;
    console.log(child);
    sql = `INSERT INTO comment VALUES(NULL,${productId},"${user}",NOW(),${commentId},"${child}",NULL);`;
    console.log("当前运行的sql是" + sql);
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//获取所有产品分类
app.get('/getAllProductSeries', (req, res) => {
    let sql = `SELECT seriesProduct FROM series`;
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
//通过产品id修改产品信息
app.post('/updateProductById', (req, res) => {
    let { productName, productSeries, introduction, message, id } = req.body;
    let sql = `UPDATE product SET productName = "${productName}",productSeries="${productSeries}",introduction="${introduction}",message="${message}" WHERE id = ${id}`;
    console.log("sql为:" + sql);
    db.query(sql, (err, result) => {
        if (err) return handleError(res);
        sendData(res, result);
    });
});
/**
 * ————————————————————————————————————————————下半部分，不需要进行修改————————————————————————————————————————————
 * */

// 监听端口  
app.listen(PORT, () => console.log(`成功开启端口 http://localhost:${PORT}`)); 